﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Lib_general
{
    public class Phieunhap
    {
        private DataSet dssp = null;
        private Exec_Database exec = new Exec_Database();
        private Function func = new Function();
        private DataTable dt = null;
        private SqlConnection con;
        private SqlCommand cmd;

        public DataSet Load_form()
        {
            try
            {
                dssp = new DataSet();
                string sql = "select a.masp,a.ten as tensp,b.ten as tendvt from dmsanpham a,dmdvt b where a.madvt=b.madvt ";
                dssp = exec.Get_data(sql);
                return dssp;
            }
            catch { return null; }
        }
        public DataTable LoadCombo(System.Windows.Forms.ComboBox combo,string valuemember,string displaymember,string table)
        {
            try
            {
                dt = exec.Get_data("select "+valuemember+","+displaymember+" from "+table+" ").Tables[0];
                func.LoadCombobox(combo, valuemember, displaymember, dt);
                return dt;
            }
            catch { return null; }
        }
        public string sHoten(string userid)
        {
            try { return exec.Get_data("select ten from dmnhanvien where manv='" + userid + "'").Tables[0].Rows[0][0].ToString(); }
            catch { return ""; }
        }
        public bool upd_nhapth(decimal _id,string _sohd,string _ngayhd,decimal _tylechietkhau,string _mancc,string _ngaynhap,string _manv,
            decimal _tongsoluong,decimal _tongtien,string _makho,int _khuyenmai,string _userid)
        {
            try
            {
                con = new SqlConnection(exec.s_connect);
                string sql = "";
                sql = "update nhapth set sohd=@_sohd,ngayhd=@_ngayhd,tylechietkhau=@_tylechietkhau,mancc=@_mancc,ngaynhap=@_ngaynhap,manv=@_manv,";
                sql += "tongsoluong=@_tongsoluong,tongtien=@_tongtien,makho=@_makho,khuyenmai=@_khuyenmai,userid=@_userid where id=@_id";
                con.Open();
                cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@_sohd", SqlDbType.NVarChar).Value = _sohd;
                cmd.Parameters.Add("@_ngayhd", SqlDbType.VarChar).Value = _ngayhd;
                cmd.Parameters.Add("@_tylechietkhau", SqlDbType.Decimal).Value = _tylechietkhau;
                cmd.Parameters.Add("@_mancc", SqlDbType.VarChar).Value = _mancc;
                cmd.Parameters.Add("@_ngaynhap", SqlDbType.VarChar).Value = _ngaynhap;
                cmd.Parameters.Add("@_manv", SqlDbType.VarChar).Value = _manv;
                cmd.Parameters.Add("@_tongsoluong", SqlDbType.Decimal).Value = _tongsoluong;
                cmd.Parameters.Add("@_tongtien", SqlDbType.Decimal).Value = _tongtien;
                cmd.Parameters.Add("@_makho", SqlDbType.VarChar).Value = _makho;
                cmd.Parameters.Add("@_khuyenmai", SqlDbType.Decimal).Value = _khuyenmai;
                cmd.Parameters.Add("@_userid", SqlDbType.VarChar).Value = _userid;
                cmd.Parameters.Add("@_id", SqlDbType.Decimal).Value = _id;
                int irecord = cmd.ExecuteNonQuery();
                if (irecord == 0)
                {
                    sql = "insert into nhapth(id,sohd,ngayhd,tylechietkhau,mancc,ngaynhap,manv,tongsoluong,tongtien,makho,khuyenmai,userid)";
                    sql += " values(@_id,@_sohd,@_ngayhd,@_tylechietkhau,@_mancc,@_ngaynhap,@_manv,@_tongsoluong,@_tongtien,@_makho,@_khuyenmai,@_userid)";
                    cmd = new SqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@_id", SqlDbType.Decimal).Value = _id;
                    cmd.Parameters.Add("@_sohd", SqlDbType.NVarChar).Value = _sohd;
                    cmd.Parameters.Add("@_ngayhd", SqlDbType.VarChar).Value = _ngayhd;
                    cmd.Parameters.Add("@_tylechietkhau", SqlDbType.Decimal).Value = _tylechietkhau;
                    cmd.Parameters.Add("@_mancc", SqlDbType.VarChar).Value = _mancc;
                    cmd.Parameters.Add("@_ngaynhap", SqlDbType.VarChar).Value = _ngaynhap;
                    cmd.Parameters.Add("@_manv", SqlDbType.VarChar).Value = _manv;
                    cmd.Parameters.Add("@_tongsoluong", SqlDbType.Decimal).Value = _tongsoluong;
                    cmd.Parameters.Add("@_tongtien", SqlDbType.Decimal).Value = _tongtien;
                    cmd.Parameters.Add("@_makho", SqlDbType.VarChar).Value = _makho;
                    cmd.Parameters.Add("@_khuyenmai", SqlDbType.Decimal).Value = _khuyenmai;
                    cmd.Parameters.Add("@_userid", SqlDbType.VarChar).Value = _userid;
                    cmd.ExecuteNonQuery();
                }
            }
            catch { return false; }
            finally { cmd.Dispose(); con.Close(); con.Dispose(); }
            return true;
        }
        public bool upd_nhapct(decimal _id, decimal _stt, string _masp, decimal _soluong, decimal _dongia, decimal _vat, decimal _thanhtien,
            decimal _khuyenmai, string _userid)
        {
            try
            {
                con = new SqlConnection(exec.s_connect);
                string sql = "";
                sql = "update nhapct set masp=@_masp,soluong=@_soluong,dongia=@_dongia,vat=@_vat,thanhtien=@_thanhtien,khuyenmai=@_khuyenmai,";
                sql += "userid=@_userid where id=@_id and stt=@_stt";
                con.Open();
                cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.Add("@_masp", SqlDbType.VarChar).Value = _masp;
                cmd.Parameters.Add("@_soluong", SqlDbType.Decimal).Value = _soluong;
                cmd.Parameters.Add("@_dongia", SqlDbType.Decimal).Value = _dongia;
                cmd.Parameters.Add("@_vat", SqlDbType.Decimal).Value = _vat;
                cmd.Parameters.Add("@_thanhtien", SqlDbType.Decimal).Value = _thanhtien;
                cmd.Parameters.Add("@_manv", SqlDbType.Decimal).Value = _khuyenmai;
                cmd.Parameters.Add("@_userid", SqlDbType.VarChar).Value = _userid;
                cmd.Parameters.Add("@_id", SqlDbType.Decimal).Value = _id;
                cmd.Parameters.Add("@_stt", SqlDbType.Decimal).Value = _stt;
                int irecord = cmd.ExecuteNonQuery();
                if (irecord == 0)
                {
                    sql = "insert into nhapct(id,stt,masp,soluong,dongia,vat,thanhtien,khuyenmai,userid)";
                    sql += " values(@_id,@_stt,@_masp,@_soluong,@_dongia,@_vat,@_thanhtien,@_khuyenmai,@_userid)";
                    cmd = new SqlCommand(sql, con);
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.Add("@_id", SqlDbType.Decimal).Value = _id;
                    cmd.Parameters.Add("@_stt", SqlDbType.Decimal).Value = _stt;
                    cmd.Parameters.Add("@_masp", SqlDbType.VarChar).Value = _masp;
                    cmd.Parameters.Add("@_soluong", SqlDbType.Decimal).Value = _soluong;
                    cmd.Parameters.Add("@_dongia", SqlDbType.Decimal).Value = _dongia;
                    cmd.Parameters.Add("@_vat", SqlDbType.Decimal).Value = _vat;
                    cmd.Parameters.Add("@_thanhtien", SqlDbType.Decimal).Value = _thanhtien;
                    cmd.Parameters.Add("@_manv", SqlDbType.Decimal).Value = _khuyenmai;
                    cmd.Parameters.Add("@_userid", SqlDbType.VarChar).Value = _userid;                    
                    cmd.ExecuteNonQuery();
                }
            }
            catch { return false; }
            finally { cmd.Dispose(); con.Close(); con.Dispose(); }
            return true;
        }
    }
}
